package com.gemptc.daoimp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.Photo_Library;
import com.gemptc.bean.PostInfoBean;
import com.gemptc.bean.Travelsbean;
import com.gemptc.bean.UserBean;
import com.gemptc.dao.ISelect;
import com.gemptc.utils.DBUtil;
import com.gemptc.utils.DataUtil;

public class SelectImp implements ISelect {
	// 通过查找图片表里的t_id（游记ID）查找到图片表里的pl_path（游记图片），用来返回游记的图片
	@Override
	public List<Photo_Library> selectPhoto(int t_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Photo_Library> photoList = new ArrayList<Photo_Library>();
		String sql = "Select * from photo_library where t_id=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, t_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int pl_id = resultSet.getInt(1);
				int user_id = resultSet.getInt(2);
				int rp_id = resultSet.getInt(3);
				int ph_id = resultSet.getInt(4);
				int s_id = resultSet.getInt(5);
				int t_id1 = resultSet.getInt(6);
				String pl_path = resultSet.getString(7);
				photoList.add(new Photo_Library(pl_id, user_id, rp_id, ph_id,
						s_id, t_id1, pl_path));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return photoList;
	}

	// 通过查找用户表里的user_id（用户ID）查找到用户表里的user_name（用户名），用来返回游记的用户名
	@Override
	public String selectUser_name(int user_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String user_name = null;
		String sql = "select user_name from users where user_id=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				user_name = resultSet.getString(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return user_name;
	}

	// 通过查找用户表里的user_id（用户ID）查找到用户表里的user_nickname（用户昵称），用来返回游记的用户名
	@Override
	public String select(int user_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String user_nickname = null;
		String sql = "select user_nickname from users where user_id=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				user_nickname = resultSet.getString(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return user_nickname;
	}

	// 通过查找用户表里的user_id（用户ID）查找到用户表里的user_imgPath（用户头像），用来返回游记的用户头像
	@Override
	public String select1(int user_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String user_imgPath = null;
		String sql = "select user_imgPath from users where user_id=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				user_imgPath = resultSet.getString(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return user_imgPath;
	}

	// 根据t_time找Travels表里的t_id
	@Override
	public int selectT_id(String t_time) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int t_id = 0;
		String sql = "SELECT t_id FROM travels WHERE t_time=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, t_time);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				t_id = resultSet.getInt(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return t_id;
	}

	// 查找游记的所有内容
	@Override
	public List<Travelsbean> getTravelsbean(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Travelsbean> list = new ArrayList<Travelsbean>();
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT * FROM travels ORDER BY t_id DESC limit ?,? ";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			// 查找到数据并 添加
			resultSet = preparedStatement.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (resultSet.next()) {
				// 查找到表travels里的第1列数据t_id
				int t_id = resultSet.getInt(1);
				int user_id = resultSet.getInt(2);

				// 查找到表travels里的第3列数据整数类型的rp_id
				int rp_id = resultSet.getInt(3);
				System.out.println("!!@@" + rp_id);
				// 查找到表travels里的第4列数据字符串类型的t_title
				String t_tittle = resultSet.getString(4);
				System.out.println("!!dddddd" + t_tittle);
				// 查找到表travels里的第5列数据字符串类型的t_time		
				String t_time =resultSet.getString(5);
				// 查找到表travels里的第6列数据字符串类型的t_content
				String t_content = resultSet.getString(6);
				// 查找到表travels里的第7列数据字符串类型的t_hidden
				boolean t_hidden = resultSet.getBoolean(7);
				String user_imgPath = select1(user_id);
				String user_nickname = select(user_id);
				Travelsbean travelsbean = new Travelsbean(t_id, user_id,
						user_nickname, rp_id, t_tittle, t_time, t_content,
						t_hidden, user_imgPath);
				list.add(travelsbean);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	// 注册用户时使用 用来查找用户注册时的输入的用户名和数据库的用户名是否重复
	@Override
	public boolean select2(String user_name) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean flag = false;// 表示当前用户名不存在，可以注册
		String sql = "select user_name from users where  user_name=? limit 1";
		try {
			preparedStatement = connection.prepareStatement(sql);
			// 设置？对应的值
			preparedStatement.setString(1, user_name);
			// 调用查询的方法
			resultSet = preparedStatement.executeQuery();
			// 如果数据库中存在当前用户输入的注册名称，则返回true，表示这个用户名已经存在，不能再注册
			// 否则返回false，表示可以注册
			while (resultSet.next()) {
				flag = true;// 当前用户名已经存在，不能注册
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return flag;
	}

	@Override
	public List<Travelsbean> getTravelsbeanMuLu(int pageNow, int user_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Travelsbean> list = new ArrayList<Travelsbean>();
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT t_id,rp_id,t_title,t_time,t_content,t_hidden "
				+ "FROM travels WHERE user_id=? limit ?,?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			// 查找到数据并 添加
			preparedStatement.setInt(1, user_id);
			preparedStatement.setInt(2, m);
			preparedStatement.setInt(3, n);
			resultSet = preparedStatement.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (resultSet.next()) {
				// 查找到表travels里的第1列数据t_id
				int t_id = resultSet.getInt(1);

				// 通过select方法 trabels里user_id查找user表里的user_id,
				// 查找到user表里的user_name，
				// 并返还赋值到trabels里user_id,得到user_name
				// 就是表travels里的第2列数据 user_id

				String user_nickname = select(user_id);
				// 查找到表travels里的第3列数据整数类型的rp_id
				int rp_id = resultSet.getInt(2);
				// 查找到表travels里的第4列数据字符串类型的t_title
				String t_tittle = resultSet.getString(3);
				// 查找到表travels里的第5列数据字符串类型的t_time
				// 因为t_time是日期类，需要通过Timestamp转换成字符串类型
				String t_time = resultSet.getString(4);
				// 查找到表travels里的第6列数据字符串类型的t_content
				String t_content = resultSet.getString(5);
				// 查找到表travels里的第7列数据字符串类型的t_hidden
				boolean t_hidden = resultSet.getBoolean(6);
				String user_imgPath = select1(user_id);
				String user_name = selectUser_name(user_id);
				Travelsbean travelsbean = new Travelsbean(t_id, user_name,
						user_nickname, rp_id, t_tittle, t_time, t_content,
						t_hidden, user_imgPath);
				list.add(travelsbean);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	@Override
	public boolean Collect(int user_id, int t_id, int Choice) {
		PreparedStatement preparedStatement = null;
		Connection connection = DBUtil.getConnection();
		ResultSet resultSet = null;
		String sql = null;
		switch (Choice) {
		// 添加游记收藏
		case 1:
			sql = "INSERT INTO collect (user_id,t_id) VALUES (?,?);";
			break;
		// 删除游记收藏
		case 2:
			sql = "DELETE FROM collect WHERE user_id=? AND t_id= ?;";
			break;
			//判断游记是否被收藏
		case 3:
			sql = "SELECT c_id FROM collect WHERE user_id=? AND t_id=? ;";
			break;
		}
		boolean flag = false;
		try {

			preparedStatement = connection.prepareStatement(sql);
			System.out.println(user_id+"<------->"+t_id);
			preparedStatement.setInt(1, user_id);
			preparedStatement.setInt(2, t_id);
			if (Choice == 1 || Choice == 2) {
				preparedStatement.execute();
				flag = true;
				System.out.println(flag+"添加删除");
			} else {
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					flag = true;
					System.out.println(flag+"查找");
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public List<Travelsbean> Collect(int user_id) {
		// TODO Auto-generated method stub
		return null;
	}

	// 通过用户名查用户ID
	@Override
	public int SelectUserId(String user_name) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int user_id = 0;
		String sql = "select user_id from users where user_name=? limit 1";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user_name);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				user_id = resultSet.getInt(1);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return user_id;
	}

	@Override
	public List<Travelsbean> moHuTravelsbean(String result) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Travelsbean>list=new ArrayList<Travelsbean>();
		//String sql="SELECT * FROM travels WHERE t_title  LIKE ? or t_time LIKE ? or t_content LIKE ?";
		String sql="SELECT  travels. *,users.`user_nickname`,"
				+ "users.`user_imgPath` FROM travels , users WHERE"
				+ " travels.`user_id`=users.`user_id` AND "
				+ "(travels.`t_title` LIKE binary ? OR "
				+ "travels.`t_content` LIKE binary ? OR "
				+ "travels.`t_time` LIKE binary ? OR "
				+ "users.`user_nickname` LIKE binary ?)";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1, "%"+result+"%");
			preparedStatement.setString(2, "%"+result+"%");
			preparedStatement.setString(3, "%"+result+"%");
			preparedStatement.setString(4, "%"+result+"%");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				// 查找到表travels里的第1列数据t_id
				int t_id = resultSet.getInt(1);
				int user_id = resultSet.getInt(2);
				
				// 查找到表travels里的第3列数据整数类型的rp_id
				int rp_id = resultSet.getInt(3);
				// 查找到表travels里的第4列数据字符串类型的t_title
				String t_tittle = resultSet.getString(4);
				// 查找到表travels里的第5列数据字符串类型的t_time		
				String t_time =resultSet.getString(5);
				// 查找到表travels里的第6列数据字符串类型的t_content
				String t_content = resultSet.getString(6);
				// 查找到表travels里的第7列数据字符串类型的t_hidden
				boolean t_hidden = resultSet.getBoolean(7);
				String user_nickname =resultSet.getString(8);
				String user_imgPath = resultSet.getString(9);
				Travelsbean travelsbean = new Travelsbean(t_id, user_id,
						user_nickname, rp_id, t_tittle, t_time, t_content,
						t_hidden, user_imgPath);
				list.add(travelsbean);
				System.out.println("++啊啊啊实打实大师"+list);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	//我回复的 功能
		//114通过表post_info（主贴表）reply_post（回复表）user(用户表)，传入回复的reply_user_id即谁登录谁回复的id，查询字段
		// post_info.`pi_title`, post_info.`pi_createTime`,post_info.`user_id`
		
		public List<PostInfoBean> selectreply(int reply_user_id){
			Connection con = DBUtil.getConnection();
			PreparedStatement pre = null;
			ResultSet res = null;
			List<PostInfoBean> pbList = new ArrayList<PostInfoBean>();
			//List<UserBean> list;
			String user_nickname;
			
			String sql = "SELECT post_info.`pi_title`,post_info.`pi_createTime`," +
			"post_info.`user_id`, post_info.`pi_content` ,post_info.`pi_TopicID` FROM post_info,reply_post" +
			" WHERE reply_post.`user_id` = post_info.`user_id`" +
			" AND reply_post.`pi_TopicID` = post_info.`pi_TopicID` " +
			"AND reply_post.`reply_user_id` =?;";
			
			try {
				pre = con.prepareStatement(sql);
				pre.setInt(1, reply_user_id);
				res = pre.executeQuery();
				while (res.next()) {
					String pi_title=res.getString(1);
					String pi_createTime = res.getString(2);
					int user_id = res.getInt(3);
					System.out.println("测试--------id:"+user_id);
					user_nickname=select(user_id);
					System.out.println("测试--------Nickname"+user_nickname);
					String pi_content=res.getString(4);
					int pi_TopicID=res.getInt(5);
				//	list=selectnickename(user_id);
					PostInfoBean postinfobean=new PostInfoBean(user_nickname, pi_title, pi_content, pi_createTime, pi_TopicID);				
					pbList.add(postinfobean);	
					System.out.println("111---------"+pbList);
					}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DBUtil.release(res, pre, con);
			}

			return pbList;
		}

		
		@Override
		public List<UserBean> userbean(int user_id) {
			List<UserBean> list=new ArrayList<UserBean>();
			Connection con = DBUtil.getConnection();
			PreparedStatement pre = null;
			ResultSet res = null;
			String sql = "SELECT users.`user_id`,users.`user_name`, users.`user_nickname`, users.`user_imgPath` FROM users where user_id=? ";
			try {
				pre=con.prepareStatement(sql);
				pre.setInt(1, user_id);
				res=pre.executeQuery();
				while (res.next()) {
					int user_id1=res.getInt(1);
					String user_name=res.getString(2);
					String user_nickname=res.getString(3);
					String user_imgPath=res.getString(4);
					UserBean userBean=new UserBean(
							user_id1, user_name, 
							user_nickname, user_imgPath);
					list.add(userBean);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBUtil.release(res, pre, con);
			}
			return list;
		}
}
